package com.geek.mysql7work.work1;

import com.mysql.jdbc.PreparedStatement;
import org.springframework.stereotype.Component;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.LinkedBlockingQueue;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.TimeUnit;

@Component
public class InsertTest {
    static ThreadPoolExecutor poolExecutor = new ThreadPoolExecutor(10, 20, 20, TimeUnit.SECONDS, new LinkedBlockingQueue<>(5));


    //    @PostConstruct
    public void init() {
        Connection conn = null;
        DBManagerV1 dbManagerV1 = new DBManagerV1();
        conn = dbManagerV1.getConnection(); //获取连接
        if (conn != null) {
            System.out.println("work1获取连接成功");
            InsertTest insertTest = new InsertTest();
            insertTest.insert(conn);
        } else {
            System.out.println("获取连接失败");
        }
    }

    public void insert(Connection conn) {
        CountDownLatch countDownLatch = new CountDownLatch(10);

        // 开始时间
        Long begin = System.currentTimeMillis();
        try {
            // 设置事务为非自动提交
            conn.setAutoCommit(false);
            // 比起st，pst会更好些
            PreparedStatement pst = (PreparedStatement) conn.prepareStatement(""); //准备执行语句
            // 外层循环，总提交事务次数
            for (int i = 1; i <= 10; i++) {
                poolExecutor.submit(new DemoTask(conn, countDownLatch, pst));
            }
            try {
                countDownLatch.await();
                System.out.println("核心线程" + poolExecutor.getCorePoolSize());

            } catch (InterruptedException e) {
                e.printStackTrace();
            } finally {
                // 头等连接
                try {
                    pst.close();
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        // 结束时间
        Long end = System.currentTimeMillis();
        // 耗时
        System.out.println("100万条数据插入花费时间 : " + (end - begin) + "ms");
        System.out.println("插入完成");
    }

    class DemoTask implements Runnable {

        Connection conn;
        CountDownLatch countDownLatch;
        PreparedStatement pst;

        public DemoTask(Connection conn, CountDownLatch countDownLatch, PreparedStatement pst) {
            this.conn = conn;
            this.countDownLatch = countDownLatch;
            this.pst = pst;
        }

        @Override
        public void run() {
            // 外层循环，总提交事务次数
            // sql前缀
            String prefix = "INSERT INTO geek_order (order_status,pay_status,pay_amount,buyer_name,buyer_phone,buyer_address,create_at,update_at) VALUES ";
            StringBuffer suffix = new StringBuffer();
            // 第j次提交步长
            for (int j = 1; j <= 100000; j++) {
                // 构建SQL后缀
                suffix.append("('0','0','1','buyer" + j + "','1234567890','address','2021-12-19 14:43:26','2021-12-19 14:43:26'),");
            }
            // 构建完整SQL
            String sql = prefix + suffix.substring(0, suffix.length() - 1);
            // 耗时
            // 添加执行SQL
            try {
                pst.addBatch(sql);
                pst.executeBatch();
                conn.commit();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            // 耗时
            // 清空上一次添加的数据
            countDownLatch.countDown();
        }
    }


}